---
title: "pk80001_legalnet stored procedure documentation"
summary: "Confirmed Oracle package documentation for LegalNet case, appeal, schedule, and correspondence routines."
status: draft
owner: SAIF Case Management team
last_reviewed: 2026-08-25
source_schema: "WCIS_DBA"
source_name: "pk80001_legalnet"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk80001_legalnet"
retrieval_date: "2026-08-25"
---
# pk80001_legalnet

`WCIS_DBA.pk80001_legalnet` is present in Oracle and exposes the LegalNet package routines used by this library for appeal, case-note, case-outcome, legal-case, schedule, file-location, acknowledgement, mail-date, and consolidation workflows.

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle (Case Management) |
| **Calling libraries** | `SAIF.Business.Claim.Legal` |
| **Calling classes** | `clsAcknowledge`, `clsAppeal`, `clsAppealCollection`, `clsAppealExtension`, `clsCaseNote`, `clsCaseOutcome`, `clsCaseOutcomeCollection`, `clsConsolidations`, `clsCreateFile`, `clsLegalCase`, `clsLegalCaseEvaluation`, `clsLegalFileLocation`, `clsLegalSchedule`, `clsMailDate` |

## Purpose

This package centralizes server-side persistence, retrieval, and derived-update logic for legal cases and closely related entities. The confirmed routines cover appeal and appeal-extension CRUD, case-note and case-outcome maintenance, hearing schedule retrieval, file-location history, acknowledgement updates, mail-date stamping, consolidation updates, and shared legal-case status/hearing recalculation helpers.

## Procedures / Methods

### Appeals and appeal extensions

| Routine | Type | Called by | Description |
| --- | --- | --- | --- |
| `getAppeal` | Procedure | `clsAppealCollection.MyRetrieveSQL`, `clsAppeal.BeforeRetrieve` | Returns all appeal rows for a legal case ordered by `appeal_seq_num`. |
| `insertAppeal` | Function | `clsAppeal.InsertSQL` | Inserts an `APPEAL` row including review-level, filing, brief, attorney, and linked schedule metadata. |
| `updateAppeal` | Function | `clsAppeal.UpdateSQL` | Updates the persisted appeal row and returns `SQL%ROWCOUNT`. |
| `deleteappeal` | Function | `clsAppeal.DeleteSQL` | Deletes the appeal, clears dependent extensions, optionally deletes the linked schedule, and re-evaluates downstream case/file state. |
| `getAppealExtension` | Procedure | `clsAppealExtension.MyRetrieveSQL` | Returns all extension requests for an appeal. |
| `insertAppealExtension` | Function | `clsAppealExtension.InsertSQL` | Inserts an `APPEAL_EXTENSION` row with request date and requested days. |
| `updateAppealExtension` | Function | `clsAppealExtension.UpdateSQL` | Updates an existing extension request. |

### Case notes and outcomes

| Routine | Type | Called by | Description |
| --- | --- | --- | --- |
| `GetCaseNote` | Procedure | `clsCaseNote.RetrieveSQL` | Retrieves case notes for a legal case ordered by newest `create_date` first. |
| `case_note_insert` | Function | `clsCaseNote.InsertSQL` | Inserts a case note row. |
| `case_note_update` | Function | `clsCaseNote.UpdateSQL` | Updates the category, text, and audit fields on a case note. |
| `case_note_delete` | Function | `clsCaseNote.DeleteSQL` | Deletes a case note by sequence number. |
| `GetCaseOutcome` | Procedure | `clsCaseOutcomeCollection.MyRetrieveSQL` | Returns case-outcome rows including assigned attorney and linked appeal metadata. |
| `InsertCaseOutcome` | Function | `clsCaseOutcome.InsertSQL` | Inserts a case outcome and stamps both create/update audit columns. |
| `updateCaseOutcome` | Function | `clsCaseOutcome.UpdateSQL` | Updates an existing case outcome and refreshes `update_date` to `SYSDATE`. |
| `DeleteCaseOutcome` | Function | `clsCaseOutcome.DeleteSQL` | Deletes child `OUTCOME_ISSUE` and `OUTCOME_STAFF` rows before deleting the parent outcome. |

### Legal case, schedule, file, and correspondence helpers

| Routine | Type | Called by | Description |
| --- | --- | --- | --- |
| `getLCE`, `insertLCE`, `updateLCE` | Procedure, function, function | `clsLegalCaseEvaluation` | Retrieves and persists legal-case evaluation rows. |
| `getLegalCase`, `InsertLegalCase`, `UpdateLegalCase`, `DeleteLegalCase` | Procedure, function, function, function | `clsLegalCase` | Retrieves and maintains the core legal-case header row. |
| `getSchedule`, `insertSchedule`, `updateSchedule`, `deleteSchedule` | Procedure, function, function, function | `clsLegalSchedule` | Retrieves and maintains hearing/schedule rows, including consolidated-case visibility. |
| `getFileLocation`, `insertFileLocation`, `updateFileLocation` | Procedure, function, function | `clsLegalFileLocation` | Retrieves file-location history and persists location changes. |
| `updateAcknowledged` | Function | `clsAcknowledge.BeforeUpdate` | Updates either the WCB or DCBS case number plus `acknowledge_date`. |
| `setFileCreateDate` | Procedure | `clsCreateFile.UpdateCreateFile` | Stamps file-creation metadata for a legal case. |
| `setMailOARFlag`, `setMailDate`, `mailDateList`, `setNoMailDate` | Function, procedure, procedure, function | `clsMailDate` | Supports mail-date lookup and updates for OAR and recon packet workflows. |
| `updateCaseStatus`, `updateHearingSeqNum`, `UpdateConsolidatedCase` | Procedure, procedure, procedure | `clsConsolidations`, `clsLegalCase` | Keeps consolidated cases, hearing pointers, and case status aligned after structural changes. |

## Business Rules

### BR-CLM-LGL-026 Legal case search falls through case number hierarchy

| Field | Value |
| --- | --- |
| **Statement** | `findCase` searches by `wcb_num` first, then by `dcbs_num` only when the input length is 7 or less, then by `owcp_num`, `oalj_num`, `brb_num`, and finally by `APPEAL.appeal_num`. |
| **Trigger** | Caller invokes the package search routine with a case-number-like value. |
| **Enforcement** | `findCase` executes a chained `COUNT(*)` / `OPEN recordset FOR` sequence across those identifiers. |
| **Outcome** | The first matching identifier path determines the returned legal-case recordset. |
| **Source** | `WCIS_DBA.pk80001_legalnet.findCase` |

### BR-CLM-LGL-027 Case status propagates to consolidated secondary cases

| Field | Value |
| --- | --- |
| **Statement** | Updating a primary legal case status also updates every `LEGAL_CASE` row whose sequence appears as a `CONSOLIDATED_CASE.secondary_case_seq_num` for that primary case. |
| **Trigger** | `updateCaseStatus` is called for a legal case. |
| **Enforcement** | The routine performs one direct `LEGAL_CASE` update and a second update driven by `CONSOLIDATED_CASE`. |
| **Outcome** | Consolidated secondary cases carry the same status and audit values as the primary case. |
| **Source** | `WCIS_DBA.pk80001_legalnet.updateCaseStatus` |

### BR-CLM-LGL-028 Appeal deletion cascades schedule and state cleanup

| Field | Value |
| --- | --- |
| **Statement** | Deleting an appeal first deletes its extension rows, then the appeal row, then deletes the linked schedule when one exists, recalculates the most recent hearing sequence, and may repopulate case status or file location when review-level or attorney/file-location state changes. |
| **Trigger** | `deleteappeal` is called for an appeal sequence number. |
| **Enforcement** | `deleteappeal` calls `DELETE` on `APPEAL_EXTENSION` and `APPEAL`, invokes `deleteschedule` and `updatehearingseqnum`, and conditionally calls `popcasestatus` / `popfilelocation`. |
| **Outcome** | Appeal removal cascades dependent cleanup instead of leaving orphaned schedule or file-state data. |
| **Source** | `WCIS_DBA.pk80001_legalnet.deleteappeal` |

### BR-CLM-LGL-029 Hearing sequence fallback prefers consolidated HEAR then direct HEAR then other hearing-like schedules

| Field | Value |
| --- | --- |
| **Statement** | The most-recent hearing sequence is resolved first from a consolidated primary case `HEAR` row, then from the case's own `HEAR` row, and finally from `CCALL`, `INCON`, or `MED` schedules if no `HEAR` row exists. |
| **Trigger** | `updateHearingSeqNum` recalculates `LEGAL_CASE.most_recent_hearing_seq_num`. |
| **Enforcement** | The routine performs three ordered `MAX(schedule_seq_num)` lookups before updating the case and its consolidated secondaries. |
| **Outcome** | Legal cases retain a best-available hearing pointer even when a standard `HEAR` row is absent. |
| **Source** | `WCIS_DBA.pk80001_legalnet.updateHearingSeqNum` |

### BR-CLM-LGL-030 Schedule retrieval includes consolidated-primary schedules for secondary cases

| Field | Value |
| --- | --- |
| **Statement** | `getSchedule` returns both schedules directly owned by the requested legal case and schedules from the primary case when the requested case is a consolidated secondary case. |
| **Trigger** | `getSchedule` is called for a legal case sequence number. |
| **Enforcement** | The routine `UNION`s a direct `SCHEDULE` query with a `SCHEDULE` + `CONSOLIDATED_CASE` query keyed by `secondary_case_seq_num`. |
| **Outcome** | Consolidated secondary cases see the primary hearing schedule in the returned recordset. |
| **Source** | `WCIS_DBA.pk80001_legalnet.getSchedule` |

### BR-CLM-LGL-031 Mail and acknowledgement updates are type-driven

| Field | Value |
| --- | --- |
| **Statement** | `setMailDate` updates `oar_mailed_date` only for `inType = 'OAR'` and otherwise updates `recon_packet_received_date`, while `updateAcknowledged` only updates WCB or DCBS identifiers plus `acknowledge_date`. |
| **Trigger** | Mail-date or acknowledgement routines are invoked from the library. |
| **Enforcement** | `setMailDate` branches on `inType`; `updateAcknowledged` branches on `inType = 'WCB'` and `inType = 'DCBS'`. |
| **Outcome** | Correspondence updates touch only the status/date columns associated with the caller's declared identifier type. |
| **Source** | `WCIS_DBA.pk80001_legalnet.setMailDate`, `WCIS_DBA.pk80001_legalnet.updateAcknowledged` |

## Operations

### Manage appeal records and extensions

| Field | Value |
| --- | --- |
| **Description** | Creates, updates, retrieves, and deletes `APPEAL` rows and their `APPEAL_EXTENSION` child rows, including cleanup of linked schedules during appeal deletion. |
| **Key routines** | `getAppeal`, `insertAppeal`, `updateAppeal`, `deleteappeal`, `getAppealExtension`, `insertAppealExtension`, `updateAppealExtension` |
| **Triggered by** | `clsAppeal`, `clsAppealCollection`, `clsAppealExtension` |

### Maintain case notes and outcomes

| Field | Value |
| --- | --- |
| **Description** | Retrieves and persists case notes and case outcomes, and deletes dependent issue/staff rows before removing a case outcome. |
| **Key routines** | `GetCaseNote`, `case_note_insert`, `case_note_update`, `case_note_delete`, `GetCaseOutcome`, `InsertCaseOutcome`, `updateCaseOutcome`, `DeleteCaseOutcome` |
| **Triggered by** | `clsCaseNote`, `clsCaseOutcome`, `clsCaseOutcomeCollection` |

### Maintain hearing schedules and consolidated case state

| Field | Value |
| --- | --- |
| **Description** | Retrieves schedule rows, recalculates most-recent hearing pointers, propagates primary-case status, and aligns consolidation metadata across primary and secondary cases. |
| **Key routines** | `getSchedule`, `insertSchedule`, `updateSchedule`, `deleteSchedule`, `updateCaseStatus`, `updateHearingSeqNum`, `UpdateConsolidatedCase` |
| **Triggered by** | `clsLegalSchedule`, `clsConsolidations`, `clsLegalCase` |

### Stamp acknowledgements, file creation, and mail dates

| Field | Value |
| --- | --- |
| **Description** | Updates case numbering acknowledgements, file-creation dates, file-location history, OAR flags, and mail/recon dates used by downstream legal correspondence workflows. |
| **Key routines** | `updateAcknowledged`, `setFileCreateDate`, `getFileLocation`, `insertFileLocation`, `updateFileLocation`, `setMailOARFlag`, `setMailDate`, `mailDateList`, `setNoMailDate` |
| **Triggered by** | `clsAcknowledge`, `clsCreateFile`, `clsLegalFileLocation`, `clsMailDate` |

## Calling Code References

| Procedure | Class | Method/Property | Source File | Matched Oracle routine | Match |
| --- | --- | --- | --- | --- | --- |
| `updateAcknowledged` | `clsAcknowledge` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsAcknowledge.vb` | `updateAcknowledged` | Exact |
| `insertAppeal` | `clsAppeal` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `insertAppeal` | Exact |
| `updateAppeal` | `clsAppeal` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `updateAppeal` | Exact |
| `DeleteAppeal` | `clsAppeal` | DeleteSQL/BeforeDelete | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `deleteappeal` | Exact |
| `getAppeal` | `clsAppealCollection` | MyRetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `getAppeal` | Exact |
| `insertAppealExtension` | `clsAppealExtension` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `insertAppealExtension` | Exact |
| `updateAppealExtension` | `clsAppealExtension` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb` | `updateAppealExtension` | Exact |
| `GetCaseNote` | `clsCaseNote` | RetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb` | `GetCaseNote` | Exact |
| `case_note_insert` | `clsCaseNote` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb` | `case_note_insert` | Exact |
| `case_note_update` | `clsCaseNote` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb` | `case_note_update` | Exact |
| `case_note_delete` | `clsCaseNote` | DeleteSQL/BeforeDelete | `SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb` | `case_note_delete` | Exact |
| `GetCaseOutcome` | `clsCaseOutcomeCollection` | MyRetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb` | `GetCaseOutcome` | Exact |
| `InsertCaseOutcome` | `clsCaseOutcome` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb` | `InsertCaseOutcome` | Exact |
| `updateCaseOutcome` | `clsCaseOutcome` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb` | `updateCaseOutcome` | Exact |
| `DeleteCaseOutcome` | `clsCaseOutcome` | DeleteSQL/BeforeDelete | `SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb` | `DeleteCaseOutcome` | Exact |
| `getLCE` | `clsLegalCaseEvaluation` | MyRetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb` | `getLCE` | Exact |
| `InsertLCE*` | `clsLegalCaseEvaluation` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb` | `insertLCE` | Exact |
| `updateLCE*` | `clsLegalCaseEvaluation` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb` | `updateLCE` | Exact |
| `getFileLocation` | `clsLegalFileLocation` | MyRetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb` | `getFileLocation` | Exact |
| `insert proc* (name not captured)` | `clsLegalFileLocation` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb` | `insertFileLocation` | Inferred from class responsibility* |
| `update proc* (name not captured)` | `clsLegalFileLocation` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb` | `updateFileLocation` | Inferred from class responsibility* |
| `getSchedule*` | `clsLegalSchedule` | RetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb` | `getSchedule` | Exact |
| `insertSchedule*` | `clsLegalSchedule` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb` | `insertSchedule` | Exact |
| `updateSchedule*` | `clsLegalSchedule` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb` | `updateSchedule` | Exact |
| `deleteSchedule*` | `clsLegalSchedule` | DeleteSQL/BeforeDelete | `SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb` | `deleteSchedule` | Exact |
| `getLegalCase*` | `clsLegalCase` | RetrieveSQL/BeforeRetrieve | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb` | `getLegalCase` | Exact |
| `InsertLegalCase*` | `clsLegalCase` | InsertSQL/BeforeInsert | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb` | `InsertLegalCase` | Exact |
| `UpdateLegalCase*` | `clsLegalCase` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb` | `UpdateLegalCase` | Exact |
| `DeleteLegalCase*` | `clsLegalCase` | DeleteSQL/BeforeDelete | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb` | `DeleteLegalCase` | Exact |
| `updateHearingSeqNum` | `clsLegalCase` | UpdateHearingSeq (custom method) | `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb` | `updateHearingSeqNum` | Exact |
| `setFileCreateDate` | `clsCreateFile` | UpdateCreateFile (custom method) | `SAIF.Business.Claim.Legal\LegalNet\clsCreateFile.vb` | `setFileCreateDate` | Exact |
| `setMailOARFlag` | `clsMailDate` | UpdateSQL/BeforeUpdate | `SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb` | `setMailOARFlag` | Exact |
| `setMailDate*` | `clsMailDate` | setMailDate (list-level custom method) | `SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb` | `setMailDate` | Exact |
| `UpdateConsolidatedCase` | `clsConsolidations` | custom method | `SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb` | `UpdateConsolidatedCase` | Exact |
| `updateCaseStatus` | `clsConsolidations` | custom method | `SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb` | `updateCaseStatus` | Exact |

## Source Code

<details>
<summary>Appeal and appeal-extension excerpts</summary>

```sql
PROCEDURE getAppeal(LegalCaseID IN NUMBER,
     Recordset OUT ref_cursor) AS
   BEGIN
    OPEN Recordset FOR
    SELECT appeal_seq_num AppealID,
     legal_case_seq_num LegalCaseID,
     REVIEW_LEVEL_CODE ReviewLevelCode,
     review_due_date ReviewDueDate,
     review_filed_date ReviewFiledDate,
     PETITIONER_RESPONDENT_CODE PetitionerRespondentCode,
     approved_flag ApprovedFlag,
     atty_emplid AttorneyEmployeeID,
     brief1_due_date Brief1DueDate,
     brief1_filed_date Brief1FiledDate,
     brief2_due_date Brief2DueDate,
     brief2_filed_date Brief2FiledDate,
     brief3_due_date Brief3DueDate,
     brief3_filed_date Brief3FiledDate,
     appeal_num AppealNumber,
     schedule_seq_num ScheduleID,
     create_date CreateDate,
     create_user CreateUser,
     update_date UpdateDate,
     update_user UpdateUser
   FROM APPEAL
     WHERE legal_case_seq_num = LegalCaseID
     ORDER BY appeal_seq_num;

   END getAppeal;

Function insertAppeal(AppealID IN NUMBER, LegalCaseID IN NUMBER,
            reviewLevel IN VARCHAR2, dueDate IN DATE, filedDate IN DATE,
            PetitionerRespondentCode IN VARCHAR2, approvedFlag IN VARCHAR2,
            attyEmplid IN VARCHAR2,
            brief1DueDate IN DATE, brief1FiledDate IN DATE,
            brief2DueDate IN DATE, brief2FiledDate IN DATE,
            brief3DueDate IN DATE, brief3FiledDate IN DATE,
            appealNum IN VARCHAR2, ScheduleID IN NUMBER,
            createDate IN DATE, createUser IN VARCHAR2)
             RETURN NUMBER
     AS BEGIN
         INSERT INTO APPEAL
    (appeal_seq_num, legal_case_seq_num, REVIEW_LEVEL_CODE,
     review_due_date, review_filed_date,
     PETITIONER_RESPONDENT_CODE, approved_flag,
     atty_emplid, brief1_due_date, brief1_filed_date,
     brief2_due_date, brief2_filed_date,
     brief3_due_date, brief3_filed_date,
     appeal_num, schedule_seq_num, create_date, create_user)
  VALUES
    (AppealID, LegalCaseID,
            reviewLevel, dueDate, filedDate,
            PetitionerRespondentCode, approvedFlag,
            attyEmplid,
            brief1DueDate, brief1FiledDate,
            brief2DueDate, brief2FiledDate,
            brief3DueDate, brief3FiledDate,
            appealNum, ScheduleID,
            createDate, createUser);
  Return SQL%Rowcount;
  EXCEPTION
       WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20004,
         'Error in insertAppeal: '||SQLERRM);

  END insertAppeal;

Function deleteappeal (inlegalcaseseqnum IN NUMBER, inappealseqnum IN NUMBER)
  RETURN NUMBER
AS
 v_atty           APPEAL.atty_emplid%TYPE;
 v_review_level   APPEAL.REVIEW_LEVEL_CODE%TYPE;
 v_sched_seq      APPEAL.schedule_seq_num%TYPE;
 v_prior_level    APPEAL.REVIEW_LEVEL_CODE%TYPE;
 v_prior_atty     APPEAL.atty_emplid%TYPE;
 v_prior_appeal   APPEAL.appeal_seq_num%TYPE;
 v_loc_emplid     LEGAL_FILE_LOCATION.location_person_emplid%TYPE;
 v_loc_code       LEGAL_FILE_LOCATION.LEGAL_FILE_LOCATION_CODE%TYPE;
 v_assgn_atty     APPEAL.atty_emplid%TYPE;
 v_cnt            Number;
 v_result         Number;
BEGIN
 BEGIN
    SELECT atty_emplid, NVL(REVIEW_LEVEL_CODE,' '), schedule_seq_num
      INTO v_atty, v_review_level, v_sched_seq
      FROM APPEAL
     WHERE appeal_seq_num = inappealseqnum;
 END;

 BEGIN
    DELETE FROM APPEAL_EXTENSION
          WHERE appeal_seq_num = inappealseqnum;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
       NULL;
 END;

 BEGIN
    DELETE FROM APPEAL
          WHERE appeal_seq_num = inappealseqnum;
          v_result := SQL%Rowcount;
 EXCEPTION
    WHEN OTHERS THEN
       NULL;
 END;
```

</details>

<details>
<summary>Case-note and case-outcome excerpts</summary>

```sql
PROCEDURE GetCaseNote(CaseId IN  NUMBER,
                    recordset OUT ref_cursor)
AS BEGIN
OPEN recordset FOR
SELECT
      case_note_seq_num NoteId,
      legal_case_seq_num CaseId,
      NOTE_CATEGORY_CODE CategoryCode,
      text NoteText,
      create_date CreateDate,
      create_user CreateUser,
      update_date UpdateDate,
      update_user UpdateUser
  FROM CASE_NOTE
  WHERE
      legal_case_seq_num = CaseId
      order by Create_date desc;

END GetCaseNote;

Function case_note_insert
(
  noteId          IN CASE_NOTE.case_note_seq_num%TYPE,
  caseId          IN CASE_NOTE.legal_case_seq_num%TYPE,
  categoryCode    IN CASE_NOTE.NOTE_CATEGORY_CODE%TYPE,
  Notetext        IN CASE_NOTE.text%TYPE,
  createDate      IN CASE_NOTE.create_date%TYPE,
  createUser      IN CASE_NOTE.create_user%TYPE)
  RETURN NUMBER
  IS
  v_update_count NUMBER;
BEGIN
  INSERT INTO LEGAL.CASE_NOTE
  (
      case_note_seq_num,
      legal_case_seq_num,
      NOTE_CATEGORY_CODE,
      text,
      create_date,
      create_user
  )
  VALUES
  (
      noteId,
      caseId,
      categoryCode,
      Notetext,
      createdate,
      createUser
  );
  v_update_count := SQL%Rowcount;
  RETURN v_update_count;
END case_note_insert;

PROCEDURE GetCaseOutcome(LegalCaseID IN  NUMBER,
                    recordset OUT ref_Cursor)
AS BEGIN
OPEN recordset FOR
SELECT cot.case_outcome_seq_num CaseOutcomeID,
     cot.legal_case_seq_num LegalCaseID,
     cot.ORDER_TYPE_CODE OrderTypeCode,
     cot.order_signed_date OrderSignedDate,
     cot.order_filed_date OrderFiledDate,
     cot.create_date CreateDate,
     cot.create_user CreateUser,
     cot.update_date UpdateDate,
     cot.update_user UpdateUser,
     NVL(cot.asgn_atty_emplid, '0') AS AsgnAttyEmplid,
     nvl(COT.APPEAL_SEQ_NUM, 0) as appealId
FROM CASE_OUTCOME cot
WHERE cot.legal_case_seq_num = LegalCaseID
ORDER BY cot.order_signed_date;
END GetCaseOutcome;

FUNCTION DeleteCaseOutcome(case_outcome_seq_num_in IN CASE_OUTCOME.case_outcome_seq_num%TYPE) return number
AS
BEGIN

--Delete child issues
BEGIN
DELETE FROM OUTCOME_ISSUE
WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  NULL;
END;

--Delete child staff
BEGIN
DELETE FROM OUTCOME_STAFF
WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  NULL;
END;

--Now delete the outcome
BEGIN
DELETE FROM CASE_OUTCOME
WHERE CASE_OUTCOME_SEQ_NUM = case_outcome_seq_num_in;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  NULL;
END;
RETURN SQL%Rowcount;
END DeleteCaseOutcome;
```

</details>

<details>
<summary>Schedule, consolidation, and correspondence excerpts</summary>

```sql
PROCEDURE getSchedule(LegalCaseSeqNum IN NUMBER,
    recordset OUT ref_cursor) AS
  BEGIN
   OPEN Recordset FOR
   SELECT schedule_seq_num as ScheduleSeqNum,
            legal_case_seq_num as LegalCaseSeqNum,
            schedule_date as ScheduleDate,
            schedule_time as ScheduleTime,
            alj_person_seq_num as AljPersonSeqNum,
            hearing_location_Seq_num as HearingLocationSeqNum,
            asgn_attorney_emplid as AsgnAttorneyEmplid,
            DURATION_CODE as DurationCode,
            SCHEDULE_TYPE_CODE as ScheduleTypeCode,
            SCHEDULE_STATUS_CODE as ScheduleStatusCode,
            KEEP_ATTORNEY_FLAG as KeepAttorneyFlag,
            assigned_date as AssignedDate,
            reminder_date as ReminderDate,
            schedule_text as ScheduleText,
            create_user as CreateUser,
            create_date as CreateDate,
            update_user as UpdateUser,
            update_date as UpdateDate,
            asgn_other_firm_seq_num as AsgnOtherFirmSeqNum,
            asgn_person_seq_num as AsgnPersonSeqNum
       FROM SCHEDULE
      WHERE legal_case_seq_num = LegalCaseSeqNum
    UNION
    SELECT schedule_seq_num,
            legal_case_seq_num,
            schedule_date,
            schedule_time,
            alj_person_seq_num,
            hearing_location_Seq_num,
            asgn_attorney_emplid,
            DURATION_CODE,
            SCHEDULE_TYPE_CODE,
            SCHEDULE_STATUS_CODE,
            keep_attorney_flag,
            assigned_date,
            reminder_date,
            schedule_text,
            s.create_user,
            s.create_date,
            s.update_user,
            s.update_date,
            asgn_other_firm_seq_num,
            asgn_person_seq_num
       FROM SCHEDULE s, CONSOLIDATED_CASE c
      WHERE c.secondary_case_seq_num = LegalCaseSeqNum
        AND c.primary_case_seq_num = s.legal_case_seq_num
      ORDER BY ScheduleSeqNum;

   END getSchedule;

PROCEDURE updateCaseStatus(aLegalCaseNum IN NUMBER, newStatus IN VARCHAR2,
          updateDate IN DATE, updateUser IN VARCHAR2)
      AS BEGIN
      UPDATE LEGAL_CASE
         SET CASE_STATUS_CODE = newStatus,
             update_date = updateDate,
             update_user = updateUser
       WHERE legal_case_seq_num = aLegalCaseNum;

        -- Update consolidated Cases too  ---
      UPDATE LEGAL_CASE
         SET CASE_STATUS_CODE = newStatus,
                 update_date = updateDate,
                 update_user = updateUser
       WHERE legal_case_seq_num IN
           (SELECT secondary_case_seq_num
              FROM CONSOLIDATED_CASE
             WHERE primary_case_seq_num =  aLegalCaseNum);

END updateCaseStatus;

PROCEDURE setMailDate(seqNums IN VARCHAR2, inDate IN DATE,
         inType IN VARCHAR2, userid IN VARCHAR2)
   IS
        v_updatestmt VARCHAR2(5000);

        BEGIN
   IF inType = 'OAR' THEN
      v_updatestmt := 'Update legal_case ' ||
          ' set oar_mailed_date = ''' || inDate ||
          ''' , update_date =  sysdate ' ||
          ' , update_user = ''' || userid ||
          ''' where legal_case_seq_num in (' || seqNums || ')';
        EXECUTE IMMEDIATE v_updatestmt;
   ELSE
      v_updatestmt := 'Update legal_case ' ||
          ' set recon_packet_received_date = ''' || inDate ||
          ''' , update_date =  sysdate ' ||
          ' , update_user = ''' || userid ||
          ''' where legal_case_seq_num in (' || seqNums || ')';
          EXECUTE IMMEDIATE v_updatestmt;
   END IF;

   END setMailDate;

Function updateAcknowledged(inType IN VARCHAR2, inNum IN VARCHAR2,
    inDate IN DATE, CaseId IN NUMBER)
   RETURN NUMBER
 IS
 v_update_count NUMBER;
BEGIN
    IF inType = 'WCB' THEN
       UPDATE LEGAL_CASE
          SET wcb_num = inNum,
              acknowledge_date = inDate
        WHERE legal_case_seq_num = CaseId;
    END IF;
    IF inType = 'DCBS' THEN
       UPDATE LEGAL_CASE
          SET dcbs_num = inNum,
              acknowledge_date = inDate
        WHERE legal_case_seq_num = CaseId;
    END IF;
    v_update_count := SQL%Rowcount;
 RETURN v_update_count;
 END updateAcknowledged;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources
- Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk80001_legalnet.json`
- Oracle package body/specification: `WCIS_DBA.pk80001_legalnet`
- `SAIF.Business.Claim.Legal\LegalNet\clsAcknowledge.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsAppeal.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsCaseNote.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsCaseOutcome.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsLegalCaseEvaluation.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsLegalFileLocation.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsLegalSchedule.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsLegalCase.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsCreateFile.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsMailDate.vb`
- `SAIF.Business.Claim.Legal\LegalNet\clsConsolidations.vb`